package com.dao.Impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.dao.BaseDao;
import com.dao.MusicListDao;
import com.entity.MusicList;
import com.entity.Data.PagerData;
import com.entity.Query.MusicListQuery;
import com.util.MyUtil;

@Repository
public class MusicListDaoImpl extends BaseDao implements MusicListDao{

	@Override
	public List<Map<String, Object>> queryMusicList() {
		String sql ="select ml.musicListId,ml.musicListName,ml.musicListIntro,"
				+ "ml.musicListPhoto,mlt.musicListTypeName from musiclist as ml "
				+ "join musiclisttype as mlt on ml.musicListTypeId=mlt.musicListTypeId";
		return executeQuery(sql, null);
	}

	@Override
	public Map<String, Object> queryMusicListId(int musicListId) {
		String sql ="select ml.musicListId,ml.musicListName,ml.musicListIntro,"
				+ "ml.musicListPhoto,ml.musicListTypeId,mlt.musicListTypeName from musiclist as ml "
				+ "join musiclisttype as mlt on ml.musicListTypeId=mlt.musicListTypeId where musicListId=?";
		Object[] params={musicListId};
		return executeQueryMap(sql, params);
	}

	@Override
	public int addMusicList(MusicList musicList) {
		String sql ="insert into musicList(musicListName,musicListIntro,musicListPhoto,musicListTypeId)values(?,?,?,?)";
		Object[] params={musicList.getMusicListName(),musicList.getMusicListIntro(),
				musicList.getMusicListPhoto(),musicList.getMusicListTypeId()};
		return executeUpdate(sql, params);
	}

	@Override
	public int editMusicList(MusicList musicList) {
		String sql ="update musicList	set musicListName=?,musicListIntro=?,musicListTypeId=? 	where musicListId=?";
		Object[] params={musicList.getMusicListName(),musicList.getMusicListIntro(),
				musicList.getMusicListTypeId(),musicList.getMusicListId()};
		return executeUpdate(sql, params);
	}

	@Override
	public int del(int musicListId) {
		String sql ="delete from musicLsit where musicListId=?";
		Object[] params={musicListId};
		return executeUpdate(sql, params);
	}

	@Override
	public PagerData queryByPage(MusicListQuery musicListQuery, int curPage, int pageSize) {
		//根据条件拼接where语句
		String sqlWhere = "";
		List<Object> params = new ArrayList<Object>();
		
		//名字条件不为空
		if(!MyUtil.isNull(musicListQuery.getMusicListName())){
			sqlWhere +=" and ml.musicListName like ? ";
			params.add(musicListQuery.getMusicListName());
		}
		//班级
		if(musicListQuery.getMusicListTypeId()!=null){
			sqlWhere += " and mlt.musicListTypeId = ? ";
			params.add(musicListQuery.getMusicListTypeId());
		}
		
		String sqlTotal ="select "
				+ " count(*) from musiclist as ml "
				+ " join musiclisttype as mlt "
				+ " on ml.musicListTypeId=mlt.musicListTypeId"
				+ " where 1=1 "+sqlWhere;
		int totalCount = jdbcTemplate.queryForInt(sqlTotal,params.toArray());
		//根据总记录数获取总页数
		int totalPage = totalCount%pageSize==0 ?
				        totalCount/pageSize    :
				        totalCount/pageSize+1;
		String sql ="select ml.musicListId,ml.musicListName,ml.musicListIntro,"
				+ " ml.musicListPhoto,mlt.musicListTypeName from musiclist as ml "
				+ " join musiclisttype as mlt on ml.musicListTypeId=mlt.musicListTypeId "
				+ " where 1=1 " +sqlWhere
				+ " order by ml.musicListId"
				+ " limit ?,?";
		//计算分页查询的开始位置
		int start = (curPage-1)*pageSize;
		
		params.add(start);
		params.add(pageSize);
		List<Map<String,Object>> dataList = executeQuery(sql,params.toArray());
		
		//封装分页数据对象
		PagerData pd = new PagerData();
		//当前第几页
		pd.setCurPage(curPage);
		//每页几条
		pd.setPageSize(pageSize);
		//总记录数
		pd.setTotalCount(totalCount);
		//总页数
		pd.setTotalPage(totalPage);
		//当前页数据记录
		pd.setDataList(dataList);
		return pd;
	}

}
